package com.psl.services.work.list;
import com.psl.Dao.Conn;
import com.psl.model.supplier;
import org.apache.struts2.ServletActionContext;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SupplierServices
{
  public List supplierlist()
  {
    Connection conn = new Conn().getConn();
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    List Spst = new ArrayList();
    try
    {
      pstmt = conn.prepareStatement("SELECT * from  supplier");
      rs = pstmt.executeQuery();
      while (rs.next())
      {
        supplier Sp = new supplier();
        Sp.setSp_id(rs.getInt(1));
        Sp.setSp_name(rs.getString(2));
        Sp.setSp_contact(rs.getString(3));
        Sp.setSp_position(rs.getString(4));
        Sp.setSp_tel(rs.getString(5));
        Sp.setSp_addr(rs.getString(6));
        Sp.setSp_other(rs.getString(7));
        Spst.add(Sp);
      }
      return Spst;
    }
    catch (SQLException e)
    {
      e.printStackTrace();
      return null;
    }
    finally
    {
      try
      {
        if (pstmt != null) {
          pstmt.close();
        }
        if (rs != null) {
          rs.close();
        }
        if (conn != null) {
          conn.close();
        }
      }
      catch (SQLException e)
      {
        e.printStackTrace();
      }
    }
  }
  
  public List selectsupplier()
  {
    Connection conn = new Conn().getConn();
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    List Spst = new ArrayList();
    try
    {
      pstmt = conn.prepareStatement("SELECT DISTINCT sp_id,sp_name from  supplier ");
      rs = pstmt.executeQuery();
      while (rs.next())
      {
        supplier Sp = new supplier();
        Sp.setSp_id(rs.getInt(1));
        Sp.setSp_name(rs.getString(2));
        Spst.add(Sp);
        HttpServletRequest request= ServletActionContext.getRequest();
        request.setAttribute("supplier",Spst);
      }
      return Spst;
    }
    catch (SQLException e)
    {
      e.printStackTrace();
      return null;
    }
    finally
    {
      try
      {
        if (pstmt != null) {
          pstmt.close();
        }
        if (rs != null) {
          rs.close();
        }
        if (conn != null) {
          conn.close();
        }
      }
      catch (SQLException e)
      {
        e.printStackTrace();
      }
    }
  }
  
  public List Asupplier(int sp_id)
  {
    Connection conn = new Conn().getConn();
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    List Spst = new ArrayList();
    try
    {
      pstmt = conn.prepareStatement("SELECT * from  supplier WHERE sp_id=?");
      pstmt.setInt(1, sp_id);
      rs = pstmt.executeQuery();
      if (rs.next())
      {
        supplier Sp = new supplier();
        Sp.setSp_id(rs.getInt(1));
        Sp.setSp_name(rs.getString(2));
        Sp.setSp_contact(rs.getString(3));
        Sp.setSp_position(rs.getString(4));
        Sp.setSp_tel(rs.getString(5));
        Sp.setSp_addr(rs.getString(6));
        Sp.setSp_other(rs.getString(7));
        Spst.add(Sp);
      }
      return Spst;
    }
    catch (SQLException e)
    {
      e.printStackTrace();
      return null;
    }
    finally
    {
      try
      {
        if (pstmt != null) {
          pstmt.close();
        }
        if (rs != null) {
          rs.close();
        }
        if (conn != null) {
          conn.close();
        }
      }
      catch (SQLException e)
      {
        e.printStackTrace();
      }
    }
  }
  
  public boolean add(String sp_name)
  {
    Connection conn = new Conn().getConn();
    PreparedStatement pstmt = null;
    try
    {
      pstmt = conn.prepareStatement("insert into supplier(sp_name) VALUES(?)");
      pstmt.setString(1, sp_name);
      pstmt.execute();
      return true;
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
    finally
    {
      try
      {
        if (pstmt != null) {
          pstmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      }
      catch (SQLException e)
      {
        e.printStackTrace();
      }
    }
    return true;
  }
  
  public boolean del(int sp_id)
  {
    Connection conn = new Conn().getConn();
    PreparedStatement pstmt = null;
    try
    {
      pstmt = conn.prepareStatement("DELETE from supplier where sp_id=?");
      pstmt.setInt(1, sp_id);
      pstmt.execute();
      return true;
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
    finally
    {
      try
      {
        if (pstmt != null) {
          pstmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      }
      catch (SQLException e)
      {
        e.printStackTrace();
      }
    }
    return true;
  }
  
  public boolean update(String sp_name, String sp_contact, String sp_position, String sp_tel, String sp_addr, String sp_other)
  {
    Connection conn = new Conn().getConn();
    PreparedStatement pstmt = null;
    try
    {
      pstmt = conn.prepareStatement("UPDATE supplier set sp_name=?,sp_addr=?,sp_tel=?,sp_phone=?,sp_email=?,sp_contact=?,sp_country=?,sp_status=? WHERE sp_id=?");
      pstmt.setString(1, sp_name);
      pstmt.setString(2, sp_contact);
      pstmt.setString(3, sp_position);
      pstmt.setString(4, sp_tel);
      pstmt.setString(5, sp_addr);
      pstmt.setString(6, sp_other);
      pstmt.executeUpdate();
      return true;
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
    finally
    {
      try
      {
        if (pstmt != null) {
          pstmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      }
      catch (SQLException e)
      {
        e.printStackTrace();
      }
    }
    return true;
  }
}
